﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using ZX.Common;
using ZX.BLL.Extend;
using System.Data.OleDb;
using System.IO;
using ZX.EHR.Library;
using System.Data.SqlClient;
using ZX.BLL;

namespace ZX.WebApp.Account
{
    public partial class ExcelImport :  ZX.Web.UI.Controls.BasePage
    {
        BaseBussnies bll = new BaseBussnies();
        /// <summary>
        /// 窗体加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            this.fpReport.Attributes.Add("onchange", "return checkFileExtension(this);");
        }

        /// <summary>
        /// 确定导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnOK_Click(object sender, EventArgs e)
        {
            if (this.drpReport.SelectedValue == "00")
            {
                return;
            }
            else if (this.fpReport.PostedFile.FileName.ToString() == null || this.fpReport.PostedFile.FileName.ToString() == "")
            {
                return;
            }
            else
            {
                int excelType = 0;
                if (this.fpReport.PostedFile.FileName.EndsWith(".xls"))
                {
                    excelType = 0;
                }
                else if (this.fpReport.PostedFile.FileName.EndsWith(".xlsx"))
                {
                    excelType = 1;
                }
                //上传                
               DataTable dt = this.ExcelToDataTable(UploadFile(this.HiddenField1.Value.ToString().Trim()), excelType);
               //  DataTable dt = this.ExcelToDataTable(this.HiddenField1.Value.ToString().Trim(), excelType);
                if (dt != null && dt.Rows.Count > 0)
                {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                             try
                            {
                        
                                        string value = ",";
                                        #region-------根据列名得到值-------
                                        for (int j = 0; j < dt.Columns.Count; j++) //根据列名得到值
                                        {
                                            if (j == 1)
                                            {
                                                value += ExtendMethods.GetMd5Hash(dt.Rows[i][j].ToString()) + ",";
                                            }
                                            else if (j == 5 && dt.Rows[i][5].ToString() == "是")
                                            {
                                                value += 1 + ",";
                                            }
                                            else if (j == 5 && dt.Rows[i][5].ToString() == "否")
                                            {
                                                value += 0 + ",";
                                            }
                                            else
                                            {
                                                value += dt.Rows[i][j].ToString() + ",";
                                            }
                                        }
                                        #endregion
                                        value = value.Remove(0, 1);
                                        int n = value.LastIndexOf(',');
                                        value = value.Remove(n);    //移除最后一个逗号
                                        string[] arrayValue = value.Split(',');
                                        #region-------执行存储过程插入数据-------
                                        DatUser   du=(DatUser)System.Web.HttpContext.Current.Session["User"];
                                        string userid="00000000-0000-0000-0000-000000000000";
                                        if(du !=null)
                                        {
                                            userid=du.AddUser.ToString();
                                        }
                                        string strSql = "";
                                        string  returnstr="";
                                        if (this.drpReport.SelectedValue == "OutUser" )
                                        {
                                             strSql = string.Format("Exec sp_ImportExcel '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}'",
                                                                       arrayValue[0].ToString(), arrayValue[1].ToString(), arrayValue[2].ToString(),
                                                                       arrayValue[3].ToString(), arrayValue[4].ToString(), arrayValue[5].ToString(),
                                                                       arrayValue[6].ToString(), "", "", "", "", 0,"",userid,returnstr);
                                             returnstr= ExecPro(arrayValue[0].ToString(), arrayValue[1].ToString(), arrayValue[2].ToString(),arrayValue[3].ToString(), arrayValue[4].ToString(), Convert.ToInt16(arrayValue[5].ToString()),arrayValue[6].ToString(), "", "", "", "", 0,"",userid);
                           
                                        }
                                        else if (this.drpReport.SelectedValue == "InUser" )
                                        {
                                            strSql = string.Format("Exec sp_ImportExcel '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}'",
                                                                       arrayValue[0].ToString(), arrayValue[1].ToString(), arrayValue[2].ToString(),
                                                                       "", arrayValue[7].ToString(),
                                                                       "", "", arrayValue[3].ToString(), arrayValue[4].ToString(),
                                                                       arrayValue[5].ToString(), arrayValue[6].ToString(), 1, arrayValue[8].ToString(), userid, returnstr);

                                             returnstr = ExecPro(arrayValue[0].ToString(), arrayValue[1].ToString(), arrayValue[2].ToString(), "", arrayValue[7].ToString(), 0, "", arrayValue[3].ToString(), arrayValue[4].ToString(), arrayValue[5].ToString(), arrayValue[6].ToString(), 1, arrayValue[8].ToString(), userid);
                                        }
                           
                                        if (i == 0)
                                        {   
                                            this.lblMsg.Text = "第"+(i+1).ToString()+"笔记录，结果："+returnstr  ;
                                        }
                                        else
                                        {
                                            this.lblMsg.Text = this.lblMsg.Text + "<br/>" + "第" + (i + 1).ToString() + "笔记录，结果：" + returnstr;
                                        }
                                 
                                        //    }                               
                                        //}
                                        #endregion
                                    }
                             catch
                             {
                                 this.lblMsg.Text = this.lblMsg.Text + "<br/>"  + "第" + (i + 1).ToString() + "笔记录，结果：" + "操作失败!请检查文件内容是否正确!！";
                                 return;
                             }
                      //  this.lblMsg.Text = "操作成功! 成功" + iSCount + "条，失败" + iFCount + "条！";
                    }
                   
                }
                else
                {
                    this.lblMsg.Text = "Excel没有数据行";
                    return;
                }
            }
        }
        /// <summary>
        /// 把Excel里的数据转换为DataTable，并返回DataTable
        /// </summary>
        /// <param name="excelPath">Excel路径</param>
        /// <param name="excelType">Excel类型[0-03版本,1-07以上版本]</param>
        /// <returns></returns>
        private DataTable ExcelToDataTable(string excelPath, int excelType)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
            if (excelType == 0)
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;IMEX=1'";
            }
            else if (excelType == 1)
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
            }
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                //string tableName = "";
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    //if (dtSheetName.Rows[k]["TABLE_NAME"].ToString() == sheet + "$")
                    //{
                    //    tableName = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    //    break;
                    //}
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }
                OleDbDataAdapter myCommand = null;
                DataTable dt = new DataTable();
                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                string strExcel = "select * from [" + strTableNames[0] + "]";
                //string strExcel = "select * from[" + tableName + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(dt);
                return dt;
            }
            catch
            {
                return new DataTable();
            }
            finally
            {
                conn.Close();
            }
        }

        private string  UploadFile(string  strs)
        {          
            HttpPostedFile postfile = ZXContext.Request.Files[0];
            string str = string.Format("F" + DateTime.Now.ToString("yyyyMMddHHmmss"),ZX.Common.Globals.GetExtension(postfile.FileName));
            //判断是否存在
            if (!Directory.Exists(ZXContext.Request.PhysicalApplicationPath + "upload"))
            {
                Directory.CreateDirectory(ZXContext.Request.PhysicalApplicationPath + "upload");//创建新路径
            }
            postfile.SaveAs(ZXContext.Request.PhysicalApplicationPath + "upload/" + str);
            return ZXContext.Request.PhysicalApplicationPath + "upload/" + str;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="loginname"></param>
        /// <param name="loginpwd"></param>
        /// <param name="username"></param>
        /// <param name="companyname"></param>
        /// <param name="mobiles"></param>
        /// <param name="isprincipal"></param>
        /// <param name="custommanager"></param>
        /// <param name="sex"></param>
        /// <param name="deptname"></param>
        /// <param name="postname"></param>
        /// <param name="rolename"></param>
        /// <param name="itype"></param>
        /// <param name="incompanyname"></param>
        /// <param name="userid"></param>
        /// <returns></returns>       

        private string ExecPro(string loginname,string loginpwd,string username,string companyname,string mobiles,int isprincipal,string custommanager,string sexs,
                               string deptname,string postname,string  rolename,int itype,string incompanyname,string userid)
        {
            string returnstr="";
            SqlParameter LoginName = new SqlParameter("@loginName", SqlDbType.NVarChar, 50);
            LoginName.Direction = ParameterDirection.Input;
            LoginName.Value = loginname;
            SqlParameter loginPwd = new SqlParameter("@loginPwd", SqlDbType.NVarChar, 50);
            loginPwd.Direction = ParameterDirection.Input;
            loginPwd.Value = loginpwd;
            SqlParameter userName = new SqlParameter("@userName", SqlDbType.NVarChar, 50);
            userName.Direction = ParameterDirection.Input;
            userName.Value = username;
            SqlParameter companyName = new SqlParameter("@companyName", SqlDbType.NVarChar, 50);
            companyName.Direction = ParameterDirection.Input;
            companyName.Value = companyname;
            SqlParameter mobile = new SqlParameter("@mobile", SqlDbType.NVarChar, 50);
            mobile.Direction = ParameterDirection.Input;
            mobile.Value = mobiles;
            SqlParameter isPrincipal = new SqlParameter("@isPrincipal", SqlDbType.Int, 50);
            isPrincipal.Direction = ParameterDirection.Input;
            isPrincipal.Value = isprincipal;
            SqlParameter customManager = new SqlParameter("@customManager", SqlDbType.NVarChar, 50);
            customManager.Direction = ParameterDirection.Input;
            customManager.Value =custommanager;
            SqlParameter sex = new SqlParameter("@sex", SqlDbType.NVarChar, 50);
            sex.Direction = ParameterDirection.Input;
            sex.Value = sexs;
            SqlParameter deptName = new SqlParameter("@deptName", SqlDbType.NVarChar, 50);
            deptName.Direction = ParameterDirection.Input;
            deptName.Value = deptname;
            SqlParameter postName = new SqlParameter("@postName", SqlDbType.NVarChar, 50);
            postName.Direction = ParameterDirection.Input;
            postName.Value =postname;
            SqlParameter roleName = new SqlParameter("@roleName", SqlDbType.NVarChar, 50);
            roleName.Direction = ParameterDirection.Input;
            roleName.Value = rolename;
            SqlParameter iType = new SqlParameter("@iType", SqlDbType.Int, 50);
            iType.Direction = ParameterDirection.Input;
            iType.Value = itype;
            SqlParameter InCompanyName = new SqlParameter("@InCompanyName", SqlDbType.NVarChar, 50);
            InCompanyName.Direction = ParameterDirection.Input;
            InCompanyName.Value = incompanyname;
            SqlParameter userids = new SqlParameter("@userid", SqlDbType.NVarChar, 50);
            userids.Direction = ParameterDirection.Input;
            userids.Value = userid;
            SqlParameter ReturnValue = new SqlParameter("@ReturnValue", SqlDbType.NVarChar, 50);
            ReturnValue.Direction = ParameterDirection.Output;
            IDataParameter[] parameters = new IDataParameter[] { LoginName, loginPwd, userName, companyName, mobile, isPrincipal, customManager, sex, deptName, postName, roleName, iType
                                                                ,InCompanyName,userids,ReturnValue};
            int IResult = 0;
            ZX.DAL.DBExtend.DbSql ExecPro = new DAL.DBExtend.DbSql();
            returnstr= ExecPro.RunProcedureReturnString("sp_ImportExcel", parameters,out IResult);

            return returnstr;
        }
    

    }
}